<?php

/**
 * AppParentsModel short summary.
 *
 * AppParentsModel description.
 *
 * @version 1.0
 * @author Administrator
 */
Namespace Api\Model;
use Think\Model;
class AppParentsModel extends Model
{
    
    private $strTokenBaseKey = "afdsjkafdasdfksajdflasdfjsdf";
    
    public function getWxReadSoundDb(){
      return MwithNoPrefix("tb_wxreadsound");
    }
    
    public function getPkDatadetailDb(){
        return MwithNoPrefix("app_pkdatadetail");
    }
    /**
     * 获取PkData表
     * @return mixed
     */
    public function getPkDataDb(){
        return  MwithNoPrefix("APP_PkData");       
    }

    /**
     * 反馈数据库
     * @return mixed
     */
    public function getFeedbackDb(){
        return  MwithNoPrefix("app_feedback");       
    }
    public function getDb(){
        return  MwithNoPrefix("app_parents");
    }
    
    public function getCollectionsDb(){
        return MwithNoPrefix("app_collections");
    }
    
    public function getConfigDb(){
        return MwithNoPrefix("app_config");
    }
    /**
     * 帮助数据库
     * @return mixed
     */
    public function getDocumentationsDb(){
        return MwithNoPrefix("app_documentations");
    }
    public function getStudentDb(){
        return  MwithNoPrefix("tb_students");
    }
    
    public function getNectarHistoryDb(){
        return MwithNoPrefix("app_nectarhistory");
    }
    public function getGoldHistoryDb(){
        return MwithNoPrefix("app_goldhistory");
    }
    public function checklogin($strPhone,$strPwd){
        $db =  $this->getDb();
        $arrP =$db->where("phone='%s' and Password = '%s'",$strPhone,$strPwd)->find();
        return $arrP;
    }
    public function getIdByToken($strToken){
        // return $strToken;
        return anniEncrypt($strToken."","D",$this->strTokenBaseKey);
    }
    public function getToken($iParentID){
        return anniEncrypt($iParentID."","E",$this->strTokenBaseKey);
        //   return $iParentID;
    }
    
    public function checkAndAddParent($strPhone,$strPwd)
    {
        $tblParents =  $this->getDb()->where("phone='%s'",$strPhone)->find();
        if($tblParents!=null)
        {
            return array("error"=>"此号码已经注册");
        }
        $arrResult =  $this->getDb()->add(array("Phone"=>$strPhone,"Password"=>$strPwd,"status"=>1));
        return array("error"=>"","user"=>$arrResult);
    }
    
    public function UpdatePassword($strPhone,$strPwd)
    {
        if($strPwd=="")
        {
            return "新密码不能为空";
        }
        $this->getDb()->where("phone='%s'",$strPhone)->save(array("Password"=>$strPwd));
        return "";
    }
    
  
    public function getStudentByUsername($strUsername){
        $tblStudent =$this->getStudentDb();
        $arrStudent=  $tblStudent->where("username='%s'",$strUsername)->field("tb_students.*")->find();
        // die($tblStudent->getLastSql());
        return $arrStudent;
    }
    public function getStudentListByParentID($iID){
        
        $arrParent = $this->getDb()->where("id=%d",$iID)->find();
        $strDefaultUsername=$this->getDefaultStudentByParentID($iID);
        $strFormatSql ="select tb_students.avatar,tb_students.username,tb_students.name,case username when '%s' then 1 else 0 end as isDefault  from TB_Students where parentid=%d and parentid>0"; //设置parent>0防止默认情况能查看所有用户
        $tblStudent =$this->getStudentDb();
        $arrStudent= $tblStudent->query($strFormatSql,$strDefaultUsername,$iID);
        //die($tblStudent->getLastSql());
        return $arrStudent;
    }
    
    public function  getDefaultStudentByParentID($iParentID)
    {
        $db = $this->getDb();
        $strFormatSql="select top 1 DefaultChildUserName from  APP_Parents p inner join TB_Students s on p.ID = s.parentid
where p.id=%d and p.DefaultChildUserName=s.username ";
        $arrList = $db->query($strFormatSql,$iParentID);
        if(count($arrList)>0)
        {
            $row=$arrList[0];
            return $row["defaultchildusername"];
        }else{
            $tblStudents= $this->getStudentDb();
            $arrUser =  $tblStudents->where("parentid=%d",$iParentID)->field("username")->find();
            $strCurrentDefault = null;
            if($arrUser!=null){
                $strCurrentDefault = $arrUser["username"];
            }
            $dbParent = $this->getDb();
            $dbParent->where("id=%d",$iParentID)->save(array("DefaultChildUserName"=>$strCurrentDefault));
        }
        
    }
    public function  setDefaultStudent($iParentID,$strStuentUsername){
        $tbl =   $this->getDb();
        $dbStudent = $this->getStudentDb();
        $arrStudent = $dbStudent->where("username='%s' and parentid=%d",$strStuentUsername,$iParentID)->find();
        if($arrStudent==null)
        {
            return "请先添加此用户";
        }
        $tbl->where("id=%d",$iParentID)->save(array("DefaultChildUserName"=>$strStuentUsername));
        return "";
    }
    public function editStudentInfo($strUsername,$arrEditStudentInfo)
    {
        $tbl = $this->getStudentDb();
        $tbl->where("username='%s'",$strUsername)->save($arrEditStudentInfo);
    }
    
    public function getCourseList($strUsername,$strPicField,$strCourseNameField,$strCourseIdField){
        $strSql= "SELECT TB_CourseType.pic as ".$strPicField.",TB_CourseType.CourseName as ".$strCourseNameField;
        $strSql.=",dbo.TB_Class.coursetype as ".$strCourseIdField." FROM dbo.TB_ClassStudent
                  INNER JOIN dbo.TB_Students ON TB_Students.username = TB_ClassStudent.studentid
                  INNER JOIN dbo.TB_Class ON TB_Class.classCode = TB_ClassStudent.classCode
                  INNER JOIN dbo.TB_CourseType ON TB_CourseType.id = TB_Class.coursetype
                  WHERE TB_ClassStudent.studentid='".$strUsername."'";
        return M()->query($strSql);
    }
    
    public function getMessageList($strUsername,$iType)
    {
        $strSql = "select * from APP_MsgContent c where c.id in (
select DISTINCT nl.msgcontentid FROM APP_MsgNotifyList nl LEFT JOIN TB_ClassStudent cs on nl.notifyclasscode = cs.classCode
where (nl.notifyusername='%s' or cs.studentid='%s')
) and c.status=1 order by c.id DESC";
        $db = M();
        $tbl = $db->query($strSql,$strUsername,$strUsername);
        //die($db->getLastSql());
        return $tbl;
    }
    
    /**
     * 获取帮助
     * @param mixed $strUsername 
     * @return mixed
     */
    public function getDocs($strUsername){
        return $this->getDocumentationsDb()->where("status = 1")->order("ord asc,id desc")->select();
    }
    public function Db2Int($dbVal)
    {
        if($dbVal==null)return 0;
        return intval($dbVal);
    }
    public function getCollections($strUsername,$iType){
        $db =  $this->getCollectionsDb();
        $strFormatBaseUrl = "http://osscdn.anniekids.net/weidiandu/%s/pic/fm.jpg";
        $arrResult=array();
        switch($iType)
        {
            case 1: // 1:磨耳朵 由于数据库结构没定，临时拿其它数据代替
                $arrResult0 = $db->query("select b.BookName,c.courseid,b.DirName from TB_WxReadBook b INNER JOIN (select courseid from APP_Collections where username='%s' and type=%d) c on b.ID=c.courseid",$strUsername,$iType);
                foreach($arrResult0 as $row)
                {
                    $arrResult[]=array(
                        "courseId"=>$this->Db2Int($row["courseid"]),
                        "name"=>$row["bookname"],
                        "imageUrl"=>sprintf($strFormatBaseUrl,$row["dirname"])                     
                        );
                }
                break; // end case 1
            
            case 2: // 2：阅读
                $arrResult0 = $db->query("select b.BookName,c.courseid,b.DirName from TB_WxReadBook b INNER JOIN (select courseid from APP_Collections where username='%s' and type=%d) c on b.ID=c.courseid",$strUsername,$iType);
                foreach($arrResult0 as $row)
                {
                    $arrResult[]=array(
                        "courseId"=>$this->Db2Int($row["courseid"]),
                        "name"=>$row["bookname"],
                        "imageUrl"=>sprintf($strFormatBaseUrl,$row["dirname"])                     
                        );
                }
                break; // end case 2
            case 3: // 3：口语
                $arrResult0 = $db->query("select b.BookName,c.courseid,b.DirName from TB_WxReadBook b INNER JOIN (select courseid from APP_Collections where username='%s' and type=%d) c on b.ID=c.courseid",$strUsername,$iType);
                foreach($arrResult0 as $row)
                {
                    $arrResult[]=array(
                        "courseId"=>$this->Db2Int($row["courseid"]),
                        "name"=>$row["bookname"],
                        "imageUrl"=>sprintf($strFormatBaseUrl,$row["dirname"])                     
                        );
                }
                break; // end case 3
        } // switch($iType)
        //die($db->getLastSql());
        return $arrResult;
    }
    function updateCollectCountCache($strUsername){
        $strSql="update tb_students set AppCollectionCount = (select count(*) from APP_Collections where username='%s') where username='%s'";
        $db = $this->getStudentDb();
        $db->execute($strSql,$strUsername,$strUsername);
    }
    
    public function collectCourse($strUsername,$iType,$courseId){
        $db =   $this->getCollectionsDb();
        $arrResult = $db->where("username='%s' and type=%d and courseId=%d",$strUsername,$iType,$courseId)->field("id")->find();
        if($arrResult!=null)
        {
            return "相同内容请不要重复收藏";
        }
        $db->add(array(
            "username"=>$strUsername, "type"=>$iType, "courseId"=>$courseId,"addtime"=>date("Y-m-d H:i:s",time())
            ));
        $this->updateCollectCountCache($strUsername);
        return "";
    }
    
    public function cancelCollection($strUsername,$iType,$courseId){
        $db =   $this->getCollectionsDb();
        $db->where("username='%s' and type=%d and courseId=%d",$strUsername,$iType,$courseId)->delete();
        $this->updateCollectCountCache($strUsername);
        return "";
    }
    
    /**
     * 取PKUser
     * @param mixed $strExceptUsername 
     * @param mixed $iBookID 
     * @param mixed $iRndUserNum 取随机用户名数量 0 不随机取 
     * @return mixed
     */
    public function getPkUserList($strExceptUsername,$iBookID,$iRndUserNum){
        $db=  $this->getStudentDb();
        $dbFilter = $db->where("username in (select username from APP_PkData where wxreadbookid = %d and username <> '%s' and status=1)",$iBookID,$strExceptUsername)->field("username,avatar,name");
        if($iRndUserNum>0){
           $dbFilter=$dbFilter->order("NEWID() desc")->limit($iRndUserNum);
        }
        $arrUserList = $dbFilter ->select();
        return $arrUserList;
    }
    /**
     * 根椐.NET规则将行号转换为对应的声音网址
     * @param mixed $strWxReadBookDirName 
     * @param mixed $iPageID 
     * @param mixed $iRowID 
     */
    function getSndUrl($strWxReadBookDirName,$iPageID,$iRowID,$strPracticeFileName,$iBookGroupID){
        /*   string strRowSoundFileName = modelSnd.PageID.Value.ToString()+ strCharNum + ".mp3";
        string strRowSoundUrl = strBaseUrl +  "/sound/" + strRowSoundFileName;
         * */
        $strBaseUrl = "http://osscdn.anniekids.net/weidiandu/".$strWxReadBookDirName."/sound/";
        $strUrl = $strBaseUrl.$iPageID.chr(ord('a')+$iRowID-1).".mp3";
        return $strUrl;
    }
    
    /**
     * 获取PK数据("pagecount","bookname",lastpkscore,booktotalscore)
     * @param mixed $strUsername 
     * @param mixed $iBookID 
     * @return mixed
     *  
     */
    public function getPkData($strUsername,$iBookID){
        $strSql="select b.PageCount,b.BookName,b.dirname
,
(select sum(score) as lastscorecache from app_pkdatadetail where username='%s' and wxreadbookid=%d)  as lastpkscore
,(select count(*) from TB_WxReadSound r where r.BookID = b.ID)*5 as booktotalscore from TB_WxReadBook b 
 where b.id=%d";
        $db = $this->getPkDataDb();
        $arrPkInfo1 =  $db->query($strSql,$strUsername,$iBookID,$iBookID);
        if($arrPkInfo1==null)
        {
            return null;
        }
        $arrPkInfo = $arrPkInfo1[0];
        $iLastScore =$arrPkInfo["lastpkscore"];
        if($iLastScore==null)$iLastScore=0;
        return array(
            "pagecount"=>$arrPkInfo["pagecount"],
            "bookname"=>$arrPkInfo["bookname"],
            "lastpkscore"=>$iLastScore,
            "booktotalscore"=>$arrPkInfo["booktotalscore"],
            "dirname"=>$arrPkInfo["dirname"]
            );
    }
    /**
     * 磨耳朵用户PkData
     * @param mixed $iBookID 
     * @param mixed $iPkType 
     * @param mixed $strPkUsername 
     * @return mixed
     */
    public function getUserPkData($iBookID,$iPkType,$strPkUsername,$strUsername){
        $bPkWithSystem = false;
        if(($iPkType==0)||($iPkType==1))
        {
            $bPkWithSystem=true;
        }
       
        $mdlLesson =new AppLessonModel();
        $db= $mdlLesson->getWxReadBookDb();
        // 首先每行录音和书本外联，得到系统本书的行信息
        // 系统行的信息和用户的录音做二次联合查询
        $strSql = "select isnull(myid,0) as myid ,myscore,myrecordurl,tempbook.PageCount,tempbook.bookgroupid,tempbook.Title,tempbook.PageID,tempbook.RowID,tempbook.DirName,tempbook.PracticeFileName,userrecord.userrecordurl from ( select s.*,b.bookgroupid,b.DirName,b.PageCount,cast(s.BookID as varchar(50))+'_'+cast(s.PageID as varchar(50))+'_'+cast(s.RowID as varchar(50)) as rowflag from TB_WxReadSound s inner join TB_WxReadBook b on s.BookID = b.ID where s.BookID=%d ) tempbook
                   left join (select d.recordurl as userrecordurl, d.pageid as userpageid,d.rowid as userrowid, cast(d.wxreadbookid as varchar(50))+'_'+cast(d.PageID as varchar(50))+'_'+cast(d.RowID as varchar(50)) as userrowflag from app_pkdatadetail d where d.username='%s' and d.wxreadbookid=%d) userrecord on userrecord.userrowflag= tempbook.rowflag
                   left join (select myd.id as myid ,myd.score as myscore, myd.recordurl as myrecordurl, myd.pageid as mypageid,myd.rowid as myrowid, cast(myd.wxreadbookid as varchar(50))+'_'+cast(myd.PageID as varchar(50))+'_'+cast(myd.RowID as varchar(50)) as myrowflag from app_pkdatadetail myd where myd.username='%s' and myd.wxreadbookid=%d) myrecord on myrecord.myrowflag= tempbook.rowflag
";
        $newdb = $this->getPkDataDb();
        $arrResult0 =   $newdb ->query($strSql,$iBookID,$strPkUsername,$iBookID,$strUsername,$iBookID)  ; 
        //die($newdb->getLastSql());
        $arrTotals =array();

        $arrResult=array();
        $arrPageList = array();
        $iPageCnt = 0;
        $arrT = array();
        $mdlRecord =new AppRecordModel();
        foreach($arrResult0 as $row)
        {
            if($bPkWithSystem)
            {
                $strSoundUrl = $this->getSndUrl($row["dirname"],$row["pageid"],$row["rowid"],$row["practicefilename"],$row["bookgroupid"]);
            }else{
                $strSoundUrl =$mdlRecord->getRecordUrl($row["userrecordurl"]);
            }
            $bFinished = $row["myid"]>0 ?1:0 ;
           
            $strScore =$row["myscore"]==null?"0":$row["myscore"];
            $strSoundUrlNew = $strSoundUrl ==null?"":$strSoundUrl;
            $tempPageID = $row["pageid"];
            $arrResultSub[]=array(
                "enTitle"=>$row["title"],
                "cnTitle"=>"",
                "page"=>$row["pageid"],
                "lineId"=>$row["rowid"],
                "score"=>$strScore,
                "finished"=>$bFinished,
                "resourceUrl"=>$strSoundUrlNew,
                "myResourceUrl"=>$mdlRecord->getRecordUrl($row["myrecordurl"])
                );
            $arrT[] = $arrResultSub;
            $iPageCnt = $row["pagecount"];
        }
        $arrT = $arrResultSub;
        //die(json_encode($arrT));
        $arrNewResult = array();
        //die(json_encode($arrT));
        for($i=1;$i<=$iPageCnt;$i++){
            $arrNewResult[] = $this->getPkSubPageData($arrT,$i);
        }
        $arrResult = array(
            "bookTotalPages"=>$iPageCnt,
            "pageContent"=>$arrNewResult
            );
        return $arrResult;
    }
    function getPkSubPageData($arrLines,$iPage)
    {
        $iPage = trim($iPage."");
        $arrResult = array();
        $bIsHasData = false;
        foreach($arrLines as $row1)
        {
            //die(json_encode($iPage));
            $iTempPage = trim($row1["page"]."");
            if($iTempPage==$iPage)
            {
                $arrResult[]=$row1;
                $bIsHasData =true;
            }
        }
        //die(json_encode($arrResult));
        return array("page"=>$iPage,
                     "lineContent"=>$arrResult
            );
    }
    public function setPkData($username, $wxreadbookid, $pageid, $rowid, $score, $recordurl){
        // [id], [username], [wxreadbookid], [pageid], [rowid], [score], [recordurl]
        $db = $this->getPkDatadetailDb();
        $arrResult = $db->where("username='%s' and wxreadbookid=%d and pageid=%d and rowid=%d",$username,$wxreadbookid,$pageid,$rowid)->find();
        if($arrResult!=null)
        {
            $iID = $arrResult["id"];
            $db->where("id=%d",$iID)->save(
                   array(
                    "score"=>$score,
                    "recordurl"=>$recordurl
                   )
                );
        }else{
            $db->add(array(
                    "username"=>$username, 
                    "wxreadbookid"=>$wxreadbookid, 
                    "pageid"=>$pageid, 
                    "rowid"=>$rowid, 
                    "score"=>$score, 
                    "recordurl"=>$recordurl
                ));
        } // end if($arrResult!=null)
        $iSndCnt = $this->getWxReadSoundCount($wxreadbookid);
        $arrSumData = $this->getPkDataDetailSumData($username,$wxreadbookid);
        $iUserCnt = $arrSumData["cnt"];
        $iSumScore = $arrSumData["sumscore"];
        $bIsFinishRecord = $iSndCnt==$iUserCnt;
        if($bIsFinishRecord)
        {
            $pkDB =$this->getPkDataDb();
            $arrRes0 = $pkDB->where("username='%s' and wxreadbookid=%d",$username,$wxreadbookid)->field("id")->find();
            $arrData=array(
                    "username"=>$username,
                    "wxreadbookid"=>$wxreadbookid,
                    "lastscorecache"=>$iSumScore,
                    "status"=>1
                    );
            if($arrRes0==null)
            {
                $pkDB->add($arrData);
            }else{
                $iDbId = $arrRes0["id"];
                $pkDB->where("id=%d",$iDbId)->save($arrData);
            }
        }
       
    }
    ///**
    // * 设置PkData行
    // * [id], [username], [wxreadbookid], [lastscorecache], [status]
    // */
    //public function setPkDataInfo($strUsername,$wxreadbookid,$lastscorecache,$status){
    //    $arrResult = $db->where("username = '%s' and wxreadbookid=%d",$strUsername,$wxreadbookid)
    //}
    /**
     * 获取PkData汇总数据
     * cnt,sumscore
     * @param mixed $strUsername 
     * @param mixed $iBookID 
     * @return mixed
     */
    public function getPkDataDetailSumData($strUsername,$iBookID)
    {
        $strFormatSql = "select count(*) as cnt,ISNULL(sum(score),0) as sumscore from app_pkdatadetail where wxreadbookid=%d and username='%s'";
        $db = $this->getPkDatadetailDb();
        $arrResult = $db->query($strFormatSql,$iBookID,$strUsername);
        //die($db->getLastSql());
        $arrRow = $arrResult[0];
        return array(
            "cnt"=>$arrRow["cnt"],
            
            "sumscore"=>$arrRow["sumscore"],
            );
    }
    public function getWxReadSoundCount($iBookID){
        $db =$this->getWxReadSoundDb();
        $iSndCount =  $db->where("bookid=%d",$iBookID)->count();
        return $iSndCount;
    }
    
    
    public function addNectar($strUsername,$iCnt,$strDetail ="增减花蜜"){
        if(!is_numeric($iCnt))
        {
            return "增加的花蜜必须为数字";
        }
        if($iCnt==0)
        {
            return "增加的花蜜不能为零";
        }

        $arrUsername =   $this->getStudentByUsername($strUsername);
        if($arrUsername==null)
        {
            return "用户不存在";
        }
        $iCurrentNectar = $arrUsername["nectar"];
        $iGold = $arrUsername["gold"];       
        if($iCnt<0)
        {
           if($iCurrentNectar<abs($iCnt))
           {
               return "花蜜数量不足";
           }
        }  
        $iFinalGold = $iGold;
        $iFinalNectar = $iCurrentNectar+$iCnt;
        $NectarHistoryDb = $this->getNectarHistoryDb();
        $NectarHistoryDb->add(array(
            "detail"=>$strDetail,
            "duration"=>0,
            "addtime"=>date("Y-m-d H:i:s"),
            "count"=>$iCnt,
            "username"=>$strUsername,
            "type"=>1,
            "relateid"=>0,
            "relateitem"=>"exchangegold",
            "currentnectar"=>$iCurrentNectar,
             "finalnectar"=>$iFinalNectar,
             "exchangegold"=>0,
             "currentgold"=>$iGold,
             "finalgold"=>$iFinalGold
            ));
        $this->getStudentDb()->where("username='%s'",$strUsername)->save(array(
        "nectar"=>$iFinalNectar,
        ));
        return "";
    }
}